package com.neusoft.chenhanhua.gqing;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import org.junit.Test;

public class Pms_product_category_attribute_relation33 {
	//插入
		@Test
		public void insert() {
			Connection conn=null;
			//准备数据库用户名，密码，url，driver
			String username="root";
			String password="root";
			String driver="com.mysql.jdbc.Driver";
			String url="jdbc:mysql://localhost:3306/mall?useUnicode=true&characterEncoding=utf-8";
			try {
				Class.forName(driver);//这是属于反射的内容
				//获取连接java.sq.Connection
				conn=DriverManager.getConnection(url,username,password);
			} catch (Exception e) {
				e.printStackTrace();
			}
			//准备SQL命令
			String sql="insert into Pms_product_category_attribute_relation(id,product_category_id,product_attribute_id)values(110,2,5)";
			Statement st=null;
			try {
				//通过数据库连接conn获取事务java.sql.Statement对象st
				st=conn.createStatement();
				//执行sql命令
				int row=st.executeUpdate(sql);
				System.out.println(row>0?"添加成功":"添加失败");
			} catch (SQLException e) {
				e.printStackTrace();
			}finally {
				if (st!=null) {
					try {
						st.close();
					} catch (SQLException e) {
						e.printStackTrace();
					}
				}
				if (conn!=null) {
					try {
						st.close();
					} catch (SQLException e) {
						e.printStackTrace();
					}
				}
			}
			
		}
		//修改
		@Test
		public void update() {
			Connection conn=null;
			//准备数据库用户名，密码，url,driver
			String  username ="root";
			String password ="root";
			String url="jdbc:mysql://localhost:3306/mall?useUnicode=true&characterEncoding=utf-8";
			String drive="com.mysql.jdbc.Driver";
			//加载驱动类到虚拟机中
			try {
				Class.forName(drive);//这是属于反射的内容
				//获取数据库连接java.sql.Connection
				conn=DriverManager.getConnection(url,username,password);
			} catch (Exception e) {
				e.printStackTrace();
			}
			//准备SQL命令
			String sql="update Pms_product_category_attribute_relation set product_category_id=4 where id=1";
			Statement st=null;
			try {
				//通过数据库连接conn获取事务java.sql.statement对象st
				st=conn.createStatement();
				//执行sql命令
				int row=st.executeUpdate(sql);
				System.out.println(row>0?"修改成功":"修改失败");
			} catch (SQLException e) {
				e.printStackTrace();
			}finally {
				if (st!=null) {
					try {
						st.close();
					} catch (SQLException e) {
						e.printStackTrace();
					}
				}
				if (conn!=null) {
					try {
						st.close();
					} catch (SQLException e) {
						e.printStackTrace();
					}
				}
			}
		}
		@Test
		public void delete() {
			Connection conn=null;
			//准备数据库用户名，密码，url，driver
			String username="root";
			String password="root";
			String driver="com.mysql.jdbc.Driver";
			String url="jdbc:mysql://localhost:3306/mall?useUnicode=true&charaterEnconding=utf-8";
			try {
				Class.forName(driver);
				//获取数据库连接java.sql.Connection
				conn=DriverManager.getConnection(url,username,password);
			} catch (Exception e) {
				e.printStackTrace();
			}
			//准备SQL命令
			String sql="delete from Pms_product_category_attribute_relation where id =2";
			Statement st=null;
			try {
				//通过数据库连接conn获取事务java.sql.Statement
				st=conn.createStatement();
				int row=st.executeUpdate(sql);
				System.out.println(row>0?"删除成功":"删除失败");
			} catch (SQLException e) {
				e.printStackTrace();
			}finally {
				if (st!=null) {
					try {
						st.close();
					} catch (SQLException e) {
						e.printStackTrace();
					}
				}
				if (conn!=null) {
					try {
						st.close();
					} catch (SQLException e) {
						e.printStackTrace();
					}
				}
			}
			
		}
		@Test
		public void selet() {
			Connection conn=null;
			//准备数据库用户名，密码，url,driver
			String username="root";
			String password="root";
			String driver="com.mysql.jdbc.Driver";
			String url="jdbc:mysql://localhost:3306/mall?useUnicode=true&characterEncoding=utf-8";
			try {
				//加载驱动类到虚拟机中
				Class.forName(driver);//这属于反射的内容
				//获取数据库连接java.sql.connection
				conn=DriverManager.getConnection(url,username,password);
				
			} catch (Exception e) {
				e.printStackTrace();
			}
			//准备SQL命令
			String sql="select * from Pms_product_category_attribute_relation";
			Statement st=null;
			ResultSet rs = null;
			try {
				//通过数据库连接conn获取事务java.sql.Statement对象st
				st = conn.createStatement();
				//执行sql命令
				rs = st.executeQuery(sql);
				//处理查询结果集
				while(rs.next()) {
					//System.out.println(rs.getInt(1));
					//rs.getInt(columnLabel)：columnLabel列名或列的别名，推荐使用
					System.out.println(rs.getInt("id"));
					System.out.println(rs.getObject("product_category_id"));
					System.out.println(rs.getObject("product_attribute_id"));
					//获取username列
					//System.out.println(rs.getString("username"));
				}
			} catch (SQLException e) {
				e.printStackTrace();
			} finally {
				if(rs != null) {
					try {
						rs.close();
					} catch (SQLException e) {
						e.printStackTrace();
					}
				}
				if(st != null) {
					try {
						st.close();
					} catch (SQLException e) {
						e.printStackTrace();
					}
				}
				if(conn != null) {
					try {
						conn.close();
					} catch (SQLException e) {
						e.printStackTrace();
					}
				}
	}
		}


}
